﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using Entity.ProjectEnt;
using NPOI.HSSF.Util;


namespace NPOIDemo
{
    public class ExcelBuilder
    {
        private int index = 0;
        private ICellStyle cellStyle;
        private ICellStyle cellStyleTime;
        private ICellStyle cellStyleNum;
        private ICellStyle cellHeaderStyle;
        private void Init(HSSFWorkbook book)
        {
            cellStyle = Excel.GetICellStyle(book);

            cellStyleTime = Excel.GetICellStyle(book);
            //IDataFormat formatTime = book.CreateDataFormat();
            //cellStyleTime.DataFormat = formatTime.GetFormat("m.d");

            cellStyleNum = Excel.GetICellStyle(book);
            //IDataFormat formatNum = book.CreateDataFormat();
            //cellStyleNum.DataFormat = formatNum.GetFormat("0.00E+00");

            cellHeaderStyle = Excel.GetICellStyle(book, 22);

        }

        public HSSFWorkbook ReadExcel(List<GudaoCarEntity> gudaoCarList)
        {
            HSSFWorkbook wk = new HSSFWorkbook();
            Init(wk);
            ISheet sheet = wk.CreateSheet("站场车流表");
            ReadSheet(sheet, wk, gudaoCarList);
            return wk;
        }

        public HSSFWorkbook ReadExcelCarBox(List<CarriageBoxEntity> carriageBoxes, List<PathwayEntity> Pathways) {
            HSSFWorkbook wk = new HSSFWorkbook();
            Init(wk);
            ISheet sheet = wk.CreateSheet("箱号汇总表");
            ReadCarBoxSheet(sheet, wk, carriageBoxes, Pathways);
            return wk;
        }

        private void ReadCarBoxSheet(ISheet sheet, HSSFWorkbook wk, List<CarriageBoxEntity> carriageBoxes, List<PathwayEntity> pathways)
        {
            IRow row;
            ICell cell;



            var xiangzhuGroup = carriageBoxes.GroupBy(f => f.BoxType);
            int allColumnCount = 0;
            List<string> carAllColumnName = new List<string>();
            foreach (var xiangzhuItem in xiangzhuGroup)
            {
                string BoxType = xiangzhuItem.Key;

                var currentCarriageBoxList = xiangzhuItem.ToList();
                List<CarriageBoxEntity> carriageBoxEmptyBoxList = (from carBox in currentCarriageBoxList where carBox.Remark == "坏" select carBox).OrderByDescending(f => f.LeaveTime).ToList();//坏箱
                List<CarriageBoxEntity> carriageBoxHaveWeightBoxList = (from carBox in currentCarriageBoxList where carBox.ChangeWeight != null && carBox.Remark != "坏" select carBox).OrderByDescending(f => f.LeaveTime).ToList();//重箱
                List<CarriageBoxEntity> carriageBoxNotWeightBoxList = (from carBox in currentCarriageBoxList where carBox.ChangeWeight == null && carBox.Remark != "坏" select carBox).OrderByDescending(f => f.LeaveTime).ToList();//空箱
                allColumnCount += (carriageBoxEmptyBoxList.Count > 0 ? 1 : 0) + (carriageBoxHaveWeightBoxList.Count > 0 ? 1 : 0) + (carriageBoxNotWeightBoxList.Count > 0 ? 1 : 0);
            }


            row = sheet.CreateRow(index);
            cell = row.CreateCell(0);
            Excel.SetCellRangeAddress(sheet, 0, 0, 0, 1 + allColumnCount);//股道+allColumnCount+战场车流总计
            cell.CellStyle = cellHeaderStyle;
            cell.SetCellValue("站场车箱汇总表");

            index++;
            for (int i = 0; i < 1; i++)
            {
                row = sheet.CreateRow(index);
                row.HeightInPoints = 22;
                cell = row.CreateCell(0);
                cell.SetCellValue("股道");
                Excel.SetCellRangeAddress(sheet, 1, 2, 0, 0);
                cell.CellStyle = cellStyle;

                cell = row.CreateCell(allColumnCount + 1);
                Excel.SetCellRangeAddress(sheet, 1, 2, allColumnCount + 1, allColumnCount + 1);
                cell.SetCellValue("箱号总计");
                cell.CellStyle = cellStyle;
            }

            int cartypeCount = 0;
            int addCellRangeIndex = 1;
            int addthriedColumnIndex = 1;
            foreach (var xiangzhuItem in xiangzhuGroup)
            {
                string BoxType = xiangzhuItem.Key;

                var currentCarriageBoxList = xiangzhuItem.ToList();
                List<CarriageBoxEntity> carriageBoxEmptyBoxList = (from carBox in currentCarriageBoxList where carBox.Remark == "坏" select carBox).OrderByDescending(f => f.LeaveTime).ToList();//坏箱
                List<CarriageBoxEntity> carriageBoxHaveWeightBoxList = (from carBox in currentCarriageBoxList where carBox.ChangeWeight != null && carBox.Remark != "坏" select carBox).OrderByDescending(f => f.LeaveTime).ToList();//重箱
                List<CarriageBoxEntity> carriageBoxNotWeightBoxList = (from carBox in currentCarriageBoxList where carBox.ChangeWeight == null && carBox.Remark != "坏" select carBox).OrderByDescending(f => f.LeaveTime).ToList();//空箱
                cartypeCount = (carriageBoxEmptyBoxList.Count > 0 ? 1 : 0) + (carriageBoxHaveWeightBoxList.Count > 0 ? 1 : 0) + (carriageBoxNotWeightBoxList.Count > 0 ? 1 : 0);

                cell = row.CreateCell(addCellRangeIndex);
                cell.CellStyle = cellStyle;
                Excel.SetCellRangeAddress(sheet, 1, 1, addCellRangeIndex, cartypeCount + addCellRangeIndex - 1);
                cell.SetCellValue(BoxType);
                addCellRangeIndex += cartypeCount;
            }

            index++;
            row = sheet.CreateRow(index);
            row.HeightInPoints = 22;
            cell = row.CreateCell(0);
            addthriedColumnIndex = 1;
            List<CarBoxTemp> carBoxTemps = new List<CarBoxTemp>();
            foreach (var xiangzhuItem in xiangzhuGroup)
            {
                string BoxType = xiangzhuItem.Key;

                var currentCarriageBoxList = xiangzhuItem.ToList();
                List<CarriageBoxEntity> carriageBoxEmptyBoxList = (from carBox in currentCarriageBoxList where carBox.Remark == "坏" select carBox).OrderByDescending(f => f.LeaveTime).ToList();//坏箱
                List<CarriageBoxEntity> carriageBoxHaveWeightBoxList = (from carBox in currentCarriageBoxList where carBox.ChangeWeight != null && carBox.Remark != "坏" select carBox).OrderByDescending(f => f.LeaveTime).ToList();//重箱
                List<CarriageBoxEntity> carriageBoxNotWeightBoxList = (from carBox in currentCarriageBoxList where carBox.ChangeWeight == null && carBox.Remark != "坏" select carBox).OrderByDescending(f => f.LeaveTime).ToList();//空箱
                cartypeCount = (carriageBoxEmptyBoxList.Count > 0 ? 1 : 0) + (carriageBoxHaveWeightBoxList.Count > 0 ? 1 : 0) + (carriageBoxNotWeightBoxList.Count > 0 ? 1 : 0);
                if (carriageBoxEmptyBoxList.Count > 0)
                {
                    cell = row.CreateCell(addthriedColumnIndex);
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("坏箱");
                    addthriedColumnIndex += 1;
                    carBoxTemps.Add(new CarBoxTemp()
                    {
                        carBoxType = BoxType,
                        BoxStatus = "坏箱"
                    });
                }
                if (carriageBoxHaveWeightBoxList.Count > 0)
                {
                    cell = row.CreateCell(addthriedColumnIndex);
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("重箱");
                    addthriedColumnIndex += 1;
                    carBoxTemps.Add(new CarBoxTemp()
                    {
                        carBoxType = BoxType,
                        BoxStatus = "重箱"
                    });
                }
                if (carriageBoxNotWeightBoxList.Count > 0)
                {
                    cell = row.CreateCell(addthriedColumnIndex);
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("空箱");
                    addthriedColumnIndex += 1;
                    carBoxTemps.Add(new CarBoxTemp()
                    {
                        carBoxType = BoxType,
                        BoxStatus = "空箱"
                    });
                }
            }

            var gudaoGroupCar = carriageBoxes.GroupBy(f => f.PathWayName);
            int[] gatherDic = new int[carBoxTemps.Count];
            int gudaoCount = 0;
            foreach (var gudaoCarItem in gudaoGroupCar)
            {
                gudaoCount++;
                string gudaoName = gudaoCarItem.Key;
                index++;
                row = sheet.CreateRow(index);
                row.HeightInPoints = 22;
                cell = row.CreateCell(0);
                cell.SetCellValue(gudaoName);
                addthriedColumnIndex = 1;
                int thisCount;
                for (int i = 0; i < carBoxTemps.Count; i++) 
                {
                    thisCount = 0;
                    cell = row.CreateCell(addthriedColumnIndex++);
                    cell.CellStyle = cellStyle;
                    switch (carBoxTemps[i].BoxStatus) {
                        case "坏箱":
                            thisCount = gudaoCarItem.ToList().FindAll(f=>f.Remark == "坏"&&f.PathWayName== gudaoName&&f.BoxType== carBoxTemps[i].carBoxType).Count;
                            break;
                        case "重箱":
                            thisCount = gudaoCarItem.ToList().FindAll(f => f.Remark != "坏"&&f.ChangeWeight != null && f.PathWayName == gudaoName && f.BoxType == carBoxTemps[i].carBoxType).Count;
                            break;
                        case "空箱":
                            thisCount = gudaoCarItem.ToList().FindAll(f => f.Remark != "坏" && f.ChangeWeight == null && f.PathWayName == gudaoName && f.BoxType == carBoxTemps[i].carBoxType).Count;
                            break;
                        default:
                            thisCount = 0;
                            break;
                    }
                    if (thisCount > 0)
                    {
                        gatherDic[i] += thisCount;
                        cell.SetCellValue(thisCount);
                    }
                    else
                    {
                        cell.SetCellValue("");
                    }
                }
            }

            index++;
            row = sheet.CreateRow(index);
            row.HeightInPoints = 22;
            cell = row.CreateCell(0);
            cell.SetCellValue("合计");
            addthriedColumnIndex = 1;
            int AllCarCount = 0;
            for (int i = 0; i < carBoxTemps.Count; i++)
            {
                cell = row.CreateCell(addthriedColumnIndex++);
                cell.CellStyle = cellStyle;
                cell.SetCellValue(gatherDic[i]);
                AllCarCount += gatherDic[i];
            }

            cell = row.CreateCell(allColumnCount + 1);
            cell.SetCellValue(AllCarCount);
            cell.CellStyle = cellStyle;
        }

        private void ReadSheet(ISheet sheet, HSSFWorkbook book, List<GudaoCarEntity> gudaoCarList)
        {
            IRow row;
            ICell cell;
           
            List<AllCarAndPingmingAndType> allCarAndPingmingAndTypes = new List<AllCarAndPingmingAndType>();
            AllCarAndPingmingAndType excelTopTemp = new AllCarAndPingmingAndType();


            var kehuGroup = gudaoCarList.GroupBy(f => f.Customers);
            foreach (var kehuItem in kehuGroup)
            {
                var allCarAndPingmingAndType = new AllCarAndPingmingAndType();
                allCarAndPingmingAndType.name = kehuItem.Key;

                var customerCarList = kehuItem.ToList();//分组股道中 指定客户的车辆集合
                List<GudaoCarEntity> daizhuangList = customerCarList.FindAll(f => f.Customers.Length > 0 && f.Weight == 0);
                List<GudaoCarEntity> daipaiList = customerCarList.FindAll(f => f.Customers.Length > 0 && f.Weight > 0 && f.BelongCarriage != null && f.BelongCarriage.Weight == 0);
                List<GudaoCarEntity> daixieList = customerCarList.FindAll(f => f.Customers.Length > 0 && f.Weight > 0 && f.BelongCarriage != null && f.BelongCarriage.Weight > 0);

                if (daizhuangList.Count > 0) {
                    CarStateus daizhuangCarStateus = new CarStateus();
                    daizhuangCarStateus.StatusName = "待装";
                    var carTypeGroup = daizhuangList.GroupBy(daizhuang => daizhuang.BelongCarriage.CarType);
                    foreach (var carTypeItem in carTypeGroup)
                    {
                        CarType tempCarType = new CarType();
                        tempCarType.CarTypeNmae = carTypeItem.Key;
                        tempCarType.Count = carTypeItem.ToList().Count;
                        if (daizhuangCarStateus.carTypes == null)
                            daizhuangCarStateus.carTypes = new List<CarType>();
                        daizhuangCarStateus.carTypes.Add(tempCarType);
                        daizhuangCarStateus.ChildrenAllCount += 1;
                        allCarAndPingmingAndType.ChildrenAllCount += 1;
                    }
                    if (allCarAndPingmingAndType.carStateus == null)
                    {
                        allCarAndPingmingAndType.carStateus = new List<CarStateus>
                    {
                        daizhuangCarStateus
                    };
                    }
                    else
                    {
                        allCarAndPingmingAndType.carStateus.Add(daizhuangCarStateus);
                    }
                    allCarAndPingmingAndType.CarStatesCount += 1;
                }

                if (daipaiList.Count > 0)
                {
                    CarStateus daizhuangCarStateus = new CarStateus();
                    daizhuangCarStateus.StatusName = "待排";
                    var carTypeGroup = daipaiList.GroupBy(daizhuang => daizhuang.BelongCarriage.CarType);
                    foreach (var carTypeItem in carTypeGroup)
                    {
                        CarType tempCarType = new CarType();
                        tempCarType.CarTypeNmae = carTypeItem.Key;
                        tempCarType.Count = carTypeItem.ToList().Count;
                        if (daizhuangCarStateus.carTypes == null)
                            daizhuangCarStateus.carTypes = new List<CarType>();
                        daizhuangCarStateus.carTypes.Add(tempCarType);
                        daizhuangCarStateus.ChildrenAllCount += 1;
                        allCarAndPingmingAndType.ChildrenAllCount += 1;
                    }
                    if (allCarAndPingmingAndType.carStateus == null)
                    {
                        allCarAndPingmingAndType.carStateus = new List<CarStateus>
                    {
                        daizhuangCarStateus
                    };
                    }
                    else
                    {
                        allCarAndPingmingAndType.carStateus.Add(daizhuangCarStateus);
                    }

                    allCarAndPingmingAndType.CarStatesCount += 1;
                }

                if (daixieList.Count > 0)
                {
                    CarStateus daizhuangCarStateus = new CarStateus();
                    daizhuangCarStateus.StatusName = "待卸";
                    var carTypeGroup = daixieList.GroupBy(daizhuang => daizhuang.BelongCarriage.CarType);
                    foreach (var carTypeItem in carTypeGroup)
                    {
                        CarType tempCarType = new CarType();
                        tempCarType.CarTypeNmae = carTypeItem.Key;
                        tempCarType.Count = carTypeItem.ToList().Count;
                        if (daizhuangCarStateus.carTypes == null)
                            daizhuangCarStateus.carTypes = new List<CarType>();
                        daizhuangCarStateus.carTypes.Add(tempCarType);
                        daizhuangCarStateus.ChildrenAllCount += 1;
                        allCarAndPingmingAndType.ChildrenAllCount += 1;
                    }
                    if (allCarAndPingmingAndType.carStateus == null)
                    {
                        allCarAndPingmingAndType.carStateus = new List<CarStateus>
                    {
                        daizhuangCarStateus
                    };
                    }
                    else
                    {
                        allCarAndPingmingAndType.carStateus.Add(daizhuangCarStateus);
                    }
                    allCarAndPingmingAndType.CarStatesCount += 1;
                }
                allCarAndPingmingAndTypes.Add(allCarAndPingmingAndType);
            }

            int AllClumn = 0;
            foreach (var item in allCarAndPingmingAndTypes)
            {
                AllClumn += item.ChildrenAllCount;
            }

            row = sheet.CreateRow(index);
            cell = row.CreateCell(0);
            Excel.SetCellRangeAddress(sheet, 0, 0, 0, 1+AllClumn);//股道+AllClumn+战场车流总计
            cell.CellStyle = cellHeaderStyle;
            cell.SetCellValue("站场车流表");

            index++;
            for (int i = 0; i < 1; i++)
            {
                row = sheet.CreateRow(index);
                row.HeightInPoints = 22;
                cell = row.CreateCell(0);
                cell.SetCellValue("股道");
                Excel.SetCellRangeAddress(sheet, 1, 4, 0, 0);
                cell.CellStyle = cellStyle;

                cell = row.CreateCell(1);
                Excel.SetCellRangeAddress(sheet, 1, 1, 1, AllClumn);
                cell.SetCellValue("客户名称");
                cell.CellStyle = cellStyle;

                cell = row.CreateCell(AllClumn+1);
                Excel.SetCellRangeAddress(sheet, 1, 4, AllClumn +1, AllClumn + 1);
                cell.SetCellValue("站场车流总计");
                cell.CellStyle = cellStyle;

                index++;
                row = sheet.CreateRow(index);
                row.HeightInPoints = 22;
                cell = row.CreateCell(0);
                int addCellRangeIndex = 1;
                for (int j = 0; j < allCarAndPingmingAndTypes.Count; j++)
                {
                    cell = row.CreateCell(addCellRangeIndex);
                    cell.CellStyle = cellStyle;
                    Excel.SetCellRangeAddress(sheet, 2, 2, addCellRangeIndex, allCarAndPingmingAndTypes[j].ChildrenAllCount + addCellRangeIndex-1);
                    cell.SetCellValue(allCarAndPingmingAndTypes[j].name);
                    addCellRangeIndex += allCarAndPingmingAndTypes[j].ChildrenAllCount;
                }
            }
            index++;
            row = sheet.CreateRow(index);
            row.HeightInPoints = 22;
            cell = row.CreateCell(0);
            int cellColumnIndex = 1;
            foreach (AllCarAndPingmingAndType CarAndPingmingAndTypesitem in allCarAndPingmingAndTypes)
            {
                List<CarStateus> carStateus = CarAndPingmingAndTypesitem.carStateus;
                for (int j = 0; j < carStateus.Count; j++)
                {

                    cell = row.CreateCell(cellColumnIndex);
                    cell.CellStyle = cellStyle;
                    Excel.SetCellRangeAddress(sheet, 3,3, cellColumnIndex, carStateus[j].ChildrenAllCount + cellColumnIndex - 1);
                    cell.SetCellValue(carStateus[j].StatusName);
                    cellColumnIndex += carStateus[j].ChildrenAllCount;
                }
            }


            List<GudaoCarTypeAndStatusAndCus> exportTempList = new List<GudaoCarTypeAndStatusAndCus>();
            index++;
            row = sheet.CreateRow(index);
            row.HeightInPoints = 22;
            cell = row.CreateCell(0);
            cellColumnIndex = 1;
            foreach (AllCarAndPingmingAndType CarAndPingmingAndTypesitem in allCarAndPingmingAndTypes)
            {
                List<CarStateus> carStateus = CarAndPingmingAndTypesitem.carStateus;
                foreach (CarStateus carStateusItem in carStateus)
                {
                    foreach (CarType item in carStateusItem.carTypes)
                    {
                        cell = row.CreateCell(cellColumnIndex++);
                        cell.CellStyle = cellStyle;
                        cell.SetCellValue(item.CarTypeNmae);
                        GudaoCarTypeAndStatusAndCus temp = new GudaoCarTypeAndStatusAndCus() {
                            CarType = item.CarTypeNmae,
                            Status = carStateusItem.StatusName,
                            Customer = CarAndPingmingAndTypesitem.name
                        };
                        exportTempList.Add(temp);
                    }
                }
            }
            var groupGudaoCar = gudaoCarList.GroupBy(f => f.BelongPathway.Name);
            int[] gatherDic = new int[exportTempList.Count];
            int gudaoCount = 0;
            foreach (var gudaoCarItem in groupGudaoCar)
            {
                gudaoCount++;
                string gudaoName = gudaoCarItem.Key;
                index++;
                row = sheet.CreateRow(index);
                row.HeightInPoints = 22;
                cell = row.CreateCell(0);
                cell.SetCellValue(gudaoName);
                cellColumnIndex = 1;
                int thisCount;
                for (int i = 0; i < exportTempList.Count; i++)
                {
                    thisCount = 0;
                    cell = row.CreateCell(cellColumnIndex++);
                    cell.CellStyle = cellStyle;
                    switch (exportTempList[i].Status)
                    {
                        case "待装":
                            thisCount = gudaoCarItem.ToList().FindAll(f => f.Customers.Length > 0 && f.Weight == 0&&f.BelongCarriage!=null&&f.BelongCarriage.CarType== exportTempList[i].CarType&&f.Customers== exportTempList[i].Customer).Count;
                            break;
                        case "待卸":
                            thisCount = gudaoCarItem.ToList().FindAll(f => f.Customers.Length > 0 && f.Weight > 0 && f.BelongCarriage != null && f.BelongCarriage.Weight > 0 && f.BelongCarriage != null && f.BelongCarriage.CarType == exportTempList[i].CarType && f.Customers == exportTempList[i].Customer).Count;
                            break;
                        case "待排":
                            thisCount = gudaoCarItem.ToList().FindAll(f => f.Customers.Length > 0 && f.Weight > 0 && f.BelongCarriage != null && f.BelongCarriage.Weight == 0 && f.BelongCarriage != null && f.BelongCarriage.CarType == exportTempList[i].CarType && f.Customers == exportTempList[i].Customer).Count;
                            break;
                        default:
                            thisCount = 0;
                            break;
                    }
                    if (thisCount > 0)
                    {
                        gatherDic[i] += thisCount;
                        cell.SetCellValue(thisCount);
                    }
                    else
                    {
                        cell.SetCellValue("");
                    }

                }
            }

            index++;
            row = sheet.CreateRow(index);
            row.HeightInPoints = 22;
            cell = row.CreateCell(0);
            cell.SetCellValue("合计");
            cellColumnIndex = 1;
            int AllCarCount = 0;
            for (int i = 0; i < exportTempList.Count; i++) {
                cell = row.CreateCell(cellColumnIndex++);
                cell.CellStyle = cellStyle;
                cell.SetCellValue(gatherDic[i]);
                AllCarCount += gatherDic[i];
            }

            cell = row.CreateCell(AllClumn+1);
            cell.SetCellValue(AllCarCount);
            cell.CellStyle = cellStyle;
            //Excel.SetCellRangeAddress(sheet, 5, 5 + gudaoCount, AllClumn + 1, AllClumn + 1);


            //var groupGudaoOrderBy = gudaoCarList.GroupBy(f => f.BelongPathway.Name);
            //foreach (var item in groupGudaoOrderBy)
            //{

            //    //string gudaoName = item.Key;
            //    var listStr = item.ToList();//此股道中的车辆
            //    var OrderByCustomer = listStr.GroupBy(f => f.Customers);//按照品名分组
            //    foreach (var customerItem in OrderByCustomer)
            //    {
            //        var tempAllCarAndPingmingAndType = new AllCarAndPingmingAndType();
            //        tempAllCarAndPingmingAndType.GudaoName = item.Key;


            //        string pingMing = customerItem.Key;
            //        var customerCarList = customerItem.ToList();//分组股道中 指定客户的车辆集合
            //        List<GudaoCarEntity> daizhuangList = customerCarList.FindAll(f => f.Customers.Length > 0 && f.Weight.Equals(0.00));
            //        List<GudaoCarEntity> daipaiList = customerCarList.FindAll(f => f.Customers.Length > 0 && f.Weight > 0 && f.BelongCarriage != null && f.BelongCarriage.Equals(0.00));
            //        List<GudaoCarEntity> daixieList = customerCarList.FindAll(f => f.Customers.Length > 0 && f.Weight > 0 && f.BelongCarriage != null && f.BelongCarriage.Weight > 0);

            //        int haveDaizhuangCarStatus = (daizhuangList.Count > 0 ? 1 : 0);
            //        int haveDaipaiCarStatus = (daipaiList.Count > 0 ? 1 : 0);
            //        int haveDaixieCarStatus = (daixieList.Count > 0 ? 1 : 0);

            //        var daizhuangCarType = daizhuangList.GroupBy(daizhang => daizhang.BelongCarriage.CarType);
            //        var daipaiCarType = daipaiList.GroupBy(daizhang => daizhang.BelongCarriage.CarType);
            //        var daixieCarType = daixieList.GroupBy(daizhang => daizhang.BelongCarriage.CarType);
            //    }

            //}
        }


        public HSSFWorkbook ReadExcel(Dictionary<string, List<CarriageBoxEntity>> insertDictionary, List<CustomerAndThingsNameEntity> boxType)
        {

            HSSFWorkbook wk = new HSSFWorkbook();
            Init(wk);
            ISheet sheet = wk.CreateSheet("集装箱交接箱流表");
            ReadSheet(sheet, wk, insertDictionary, boxType);
            return wk;
        }

        private void ReadSheet(ISheet sheet, HSSFWorkbook book, Dictionary<string, List<CarriageBoxEntity>> insertDictionary, List<CustomerAndThingsNameEntity> boxType)
        {
            IRow row;
            ICell cell;
            row = sheet.CreateRow(index);
            cell = row.CreateCell(0);
            Excel.SetCellRangeAddress(sheet, 0, 0, 0, 5 + boxType.Count * 3);
            cell.CellStyle = cellHeaderStyle;
            cell.SetCellValue("集装箱交接箱流表");

            index++;
            for (int i = 0; i < 1; i++)
            {
                row = sheet.CreateRow(index);
                row.HeightInPoints = 22;
                cell = row.CreateCell(0);
                cell.SetCellValue("序号");
                Excel.SetCellRangeAddress(sheet, 1, 2, 0, 0);
                cell.CellStyle = cellStyle;

                cell = row.CreateCell(1);
                cell.SetCellValue("车号");
                cell.CellStyle = cellStyle;
                Excel.SetCellRangeAddress(sheet, 1, 2, 1, 1);

                cell = row.CreateCell(2);
                cell.SetCellValue("箱号");
                cell.CellStyle = cellStyle;
                Excel.SetCellRangeAddress(sheet, 1, 2, 2, 2);

                cell = row.CreateCell(3);
                cell.SetCellValue("蓬布号");
                cell.CellStyle = cellStyle;
                Excel.SetCellRangeAddress(sheet, 1, 2, 3, 3);

                cell = row.CreateCell(4);
                cell.SetCellValue("封号");
                cell.CellStyle = cellStyle;
                Excel.SetCellRangeAddress(sheet, 1, 2, 4, 4);
                for (int j = 0; j < boxType.Count; j++)
                {
                    cell = row.CreateCell(5 + j * 3);
                    Excel.SetCellRangeAddress(sheet, 1, 1, j * 3 + 5, j * 3 + 7);
                    cell.SetCellValue(boxType[j].Name);
                    cell.CellStyle = cellStyle;
                }
                cell = row.CreateCell(5 + boxType.Count * 3);
                cell.SetCellValue("备注");
                cell.CellStyle = cellStyle;
                Excel.SetCellRangeAddress(sheet, 1, 2, boxType.Count * 3 + 5, boxType.Count * 3 + 5);

                index++;
                row = sheet.CreateRow(index);
                row.HeightInPoints = 22;
                cell = row.CreateCell(0);
                cell.CellStyle = cellStyle;
                cell = row.CreateCell(1);
                cell.CellStyle = cellStyle;
                cell = row.CreateCell(2);
                cell.CellStyle = cellStyle;
                cell = row.CreateCell(3);
                cell.CellStyle = cellStyle;
                cell = row.CreateCell(4);
                cell.CellStyle = cellStyle;
                for (int j = 0; j < boxType.Count * 3; j += 3)
                {
                    cell = row.CreateCell(5 + j);
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("空箱");
                    cell = row.CreateCell(6 + j);
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("重箱");
                    cell = row.CreateCell(7 + j);
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("坏箱");
                }
            }

            List<CarriageBoxEntity> carriageBoxEmptyBoxList = insertDictionary["坏箱"];
            var carriageBoxGroup = carriageBoxEmptyBoxList.GroupBy(f => f.ChangeBelongCarriageId);
            int insertIndex = 1;
            foreach (var item in carriageBoxGroup)
            {
                insertIndex = InsertVaules(cell, row, sheet, item.Key, item.ToList(), insertIndex++, boxType, "坏箱");
            }

            List<CarriageBoxEntity> carriageBoxHaveWeightBoxList = insertDictionary["重箱"];
            var carriageBoxHaveWeightGroup = carriageBoxHaveWeightBoxList.GroupBy(f => f.ChangeBelongCarriageId);
            foreach (var item in carriageBoxHaveWeightGroup)
            {
                insertIndex = InsertVaules(cell, row, sheet, item.Key, item.ToList(), insertIndex++, boxType, "重箱");

            }

            List<CarriageBoxEntity> carriageBoxNotWeightBoxList = insertDictionary["空箱"];
            var carriageBoxNotWeightGroup = carriageBoxNotWeightBoxList.GroupBy(f => f.ChangeBelongCarriageId);
            foreach (var item in carriageBoxNotWeightGroup)
            {
                insertIndex = InsertVaules(cell, row, sheet, item.Key, item.ToList(), insertIndex++, boxType, "空箱");

            }
            MergeFirstAndSecondColumn(sheet,
                (2 + carriageBoxEmptyBoxList.Count + carriageBoxHaveWeightBoxList.Count +
                 carriageBoxNotWeightBoxList.Count), 2, 1);
        }
        /// <summary>
        /// 合并第一列和第二列的数据行
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="allRows">总行树</param>
        /// <param name="beginRow">开始行数</param>
        private void MergeFirstAndSecondColumn(ISheet sheet, int allRows, int beginRow, int comparisonColumnIndex)
        {
            var vaules = "";
            var vaules1 = "1";
            int merCount = 0;
            for (int i = beginRow; i < allRows; i++)
            {
                vaules = "";
                vaules1 = "1";
                var rows = sheet.GetRow(i);
                var cells = rows.GetCell(comparisonColumnIndex);
                vaules = cells.StringCellValue;

                var rows1 = sheet.GetRow(i + 1);
                var cells1 = rows1.GetCell(comparisonColumnIndex);
                vaules1 = cells1.StringCellValue;
                if (vaules.Equals(vaules1) && vaules != "" && vaules1 != "")
                {
                    Excel.SetCellRangeAddress(sheet, i, i + 1, 0, 0);
                    Excel.SetCellRangeAddress(sheet, i, i + 1, 1, 1);
                    i++;
                    merCount++;
                }
            }
            int setXuhaoVaule = 1;
            for (int i = (beginRow + 1); i < allRows; i++)
            {
                vaules = "";
                vaules1 = "1";
                var rows = sheet.GetRow(i);
                var cells = rows.GetCell(comparisonColumnIndex);
                vaules = cells.StringCellValue;

                var rows1 = sheet.GetRow(i - 1);
                var cells1 = rows1.GetCell(comparisonColumnIndex);
                vaules1 = cells1.StringCellValue;
                if (vaules.Equals(vaules1) && vaules != "" && vaules1 != "")
                {
                    continue;
                }
                else
                {
                    sheet.GetRow(i).GetCell(0).SetCellValue(new HSSFRichTextString((setXuhaoVaule++).ToString()));
                }
            }

            vaules = "";
            vaules1 = "1";
            var rowstemp = sheet.GetRow(allRows);
            var cellstemp = rowstemp.GetCell(comparisonColumnIndex);
            vaules = cellstemp.StringCellValue;
            var rowstemp1 = sheet.GetRow(allRows - 1);
            var cellstemp1 = rowstemp1.GetCell(comparisonColumnIndex);
            vaules1 = cellstemp1.StringCellValue;
            if (vaules.Equals(vaules1) && vaules != "" && vaules1 != "")
            {
            }
            else {
                sheet.GetRow(allRows).GetCell(0).SetCellValue(new HSSFRichTextString((setXuhaoVaule).ToString()));
            }
        }

        private int InsertVaules(ICell cell, IRow row, ISheet sheet, string carriageId, List<CarriageBoxEntity> carriageBoxes, int xuhao, List<CustomerAndThingsNameEntity> boxType,string ThisboxType)
        {

            foreach (var carriageItem in carriageBoxes)
            {
                row = sheet.CreateRow(index + xuhao);
                row.HeightInPoints = 22;
                cell = row.CreateCell(0);
                cell.SetCellValue(xuhao);
                cell.CellStyle = cellStyle;

                //for (int j = 3; j <= 10; j += 2)
                //{
                //    Excel.SetCellRangeAddress(sheet, j, j + 1, 0, 0);
                //    Excel.SetCellRangeAddress(sheet, j, j + 1, 1, 1);
                //}
                cell = row.CreateCell(1);
                cell.SetCellValue(carriageId);
                cell.CellStyle = cellStyleTime;

                cell = row.CreateCell(2);
                cell.SetCellValue(carriageItem.CarriageBoxID);
                cell.CellStyle = cellStyleTime;

                cell = row.CreateCell(3);
                cell.SetCellValue(carriageItem.Tarpaulin);
                cell.CellStyle = cellStyleTime;

                cell = row.CreateCell(4);
                cell.SetCellValue(carriageItem.FenHao);
                cell.CellStyle = cellStyleTime;

                int boxTypeIndex = boxType.FindIndex(f => f.Name == carriageItem.BoxType);
                
                for (int i = 0; i < boxType.Count * 3; i += 3)
                {
                    if (ThisboxType.Equals("空箱"))
                    {
                        cell = row.CreateCell(5 + i);
                        if (i == boxTypeIndex * 3 && boxTypeIndex != -1)
                        {
                            cell.SetCellValue("1");
                        }
                        else
                        {
                            cell.SetCellValue(" ");
                        }
                    }
                    else if (ThisboxType.Equals("重箱")) {
                        cell = row.CreateCell(6 + i);
                        cell.SetCellValue("");
                        if (i == boxTypeIndex * 3 && boxTypeIndex != -1)
                        {
                            cell.SetCellValue("1");
                        }
                        else
                        {
                            cell.SetCellValue(" ");
                        }
                    }
                    else if (ThisboxType.Equals("坏箱"))
                    {
                        cell = row.CreateCell(7 + i);
                        if (i == boxTypeIndex * 3 && boxTypeIndex != -1)
                        {
                            cell.SetCellValue("1");
                        }
                        else
                        {
                            cell.SetCellValue(" ");
                        }
                    }
                }
                //cell = row.CreateCell(5);
                //cell.SetCellValue("");
                //cell.CellStyle = cellStyleTime;
                //cell = row.CreateCell(6);
                //cell.SetCellValue("");
                //cell.CellStyle = cellStyleTime;
                //cell = row.CreateCell(7);
                //cell.SetCellValue("");
                //cell.CellStyle = cellStyleTime;

                //cell = row.CreateCell(8);
                //cell.SetCellValue("");
                //cell.CellStyle = cellStyleTime;
                //cell = row.CreateCell(9);
                //cell.SetCellValue("");
                //cell.CellStyle = cellStyleTime;
                //cell = row.CreateCell(10);
                //cell.SetCellValue("");
                //cell.CellStyle = cellStyleTime;

                cell = row.CreateCell(11);
                cell.SetCellValue(carriageItem.Remark);
                cell.CellStyle = cellStyleTime;
                xuhao++;
            }
            return xuhao;
        }

        public void ReadExcel()
        {

            //using (FileStream fs = File.OpenRead(@"F:/20130312.xls"))
            //{

            //    HSSFWorkbook wk = new HSSFWorkbook(fs);   //把xls文件中的数据写入wk中              

            //    ISheet sheet = wk.GetSheetAt(2);
            //    ReadSheet(sheet,wk);
            //    FileStream stream = File.OpenWrite(@"F:/20130423.xls"); ;
            //    wk.Write(stream);
            //    stream.Close();
            //}
            HSSFWorkbook wk = new HSSFWorkbook();
            Init(wk);



            ISheet sheet = wk.CreateSheet("一期");
            ReadSheet(sheet, wk);


            ISheet sheet1 = wk.CreateSheet("sheet1");
            ISheet sheet2 = wk.CreateSheet("sheet2");
            ISheet sheet3 = wk.CreateSheet("sheet3");
            ReadSheet(sheet1, wk);
            ReadSheet(sheet2, wk);
            ReadSheet(sheet3, wk, false);
            FileStream stream = File.OpenWrite(@"E:/20130423.xls"); ;
            wk.Write(stream);
            stream.Close();
            Console.WriteLine("OK");
        }

        private void ReadSheet(ISheet sheet, HSSFWorkbook book)
        {

            IRow row;

            ICell cell;
            if (true)
            {
                //index = 4;
                //row = sheet.GetRow(index);
                row = sheet.CreateRow(index);
                cell = row.CreateCell(0);
                Excel.SetCellRangeAddress(sheet, 0, 0, 0, 4);
                cell.CellStyle = cellHeaderStyle;
                cell.SetCellValue("表1-G-1-1");


                cell = row.CreateCell(5);
                Excel.SetCellRangeAddress(sheet, 0, 0, 5, 11);
                cell.CellStyle = cellHeaderStyle;
                cell.SetCellValue("2012年01月");

                index++;

                for (int i = 0; i < 1; i++)
                {
                    row = sheet.CreateRow(index);
                    row.HeightInPoints = 22;
                    cell = row.CreateCell(0);//SetCellValue("序号");
                    cell.SetCellValue("序号");
                    Excel.SetCellRangeAddress(sheet, 1, 2, 0, 0);
                    cell.CellStyle = cellStyle;

                    cell = row.CreateCell(1);//SetCellValue("序号");
                    cell.SetCellValue("排放时间");
                    cell.CellStyle = cellStyle;
                    Excel.SetCellRangeAddress(sheet, 1, 2, 1, 1);


                    cell = row.CreateCell(2);//SetCellValue("序号");
                    cell.SetCellValue("来源");
                    cell.CellStyle = cellStyle;
                    Excel.SetCellRangeAddress(sheet, 1, 2, 2, 3);

                    cell = row.CreateCell(4);//SetCellValue("序号");
                    cell.SetCellValue("体积");
                    cell.CellStyle = cellStyle;
                    Excel.SetCellRangeAddress(sheet, 1, 2, 4, 4);


                    cell = row.CreateCell(5);//SetCellValue("序号");
                    Excel.SetCellRangeAddress(sheet, 1, 1, 5, 11);
                    cell.SetCellValue("排放惰性气体");
                    cell.CellStyle = cellStyle;

                    cell = row.CreateCell(11);
                    cell.CellStyle = cellStyle;

                    index++;
                    row = sheet.CreateRow(index);
                    row.HeightInPoints = 22;
                    cell = row.CreateCell(0);
                    cell.CellStyle = cellStyle;
                    cell = row.CreateCell(1);
                    cell.CellStyle = cellStyle;
                    cell = row.CreateCell(2);
                    cell.CellStyle = cellStyle;
                    cell = row.CreateCell(3);
                    cell.CellStyle = cellStyle;
                    cell = row.CreateCell(4);
                    cell.CellStyle = cellStyle;



                    cell = row.CreateCell(5);//SetCellValue("序号");
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("Ar-4");

                    cell = row.CreateCell(6);//SetCellValue("序号");
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("Kr-85");

                    cell = row.CreateCell(7);//SetCellValue("序号");
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("Kr-85m");

                    cell = row.CreateCell(8);//SetCellValue("序号");
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("Kr-88");

                    cell = row.CreateCell(9);//SetCellValue("序号");
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("Xe-133");

                    cell = row.CreateCell(10);//SetCellValue("序号");
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("Xe-133m");

                    cell = row.CreateCell(11);//SetCellValue("序号");
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue("Xe-135");
                }


                index++;
                for (int i = 0; i < 300; i++)
                {
                    row = sheet.CreateRow(index + i);
                    row.HeightInPoints = 22;
                    cell = row.CreateCell(0);
                    cell.SetCellValue(i + 1);
                    cell.CellStyle = cellStyle;
                    if (i < 100)
                    {
                        for (int j = 3; j <= 301; j += 2)
                        {
                            Excel.SetCellRangeAddress(sheet, j, j + 1, 0, 0);
                        }
                        //Excel.SetCellRangeAddress(sheet,3,4,0,0);
                        //Excel.SetCellRangeAddress(sheet,5,6,0,0);
                        //Excel.SetCellRangeAddress(sheet,7,8,0,0);
                    }


                    cell = row.CreateCell(1);
                    cell.SetCellValue(1345);
                    cell.CellStyle = cellStyleTime;

                    cell = row.CreateCell(2);
                    cell.SetCellValue("测试");
                    Excel.SetCellRangeAddress(sheet, index + i, index + i, 2, 3);
                    cell.CellStyle = cellStyle;
                    cell = row.CreateCell(3);
                    cell.CellStyle = cellStyle;

                    cell = row.CreateCell(4);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(5);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(6);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(7);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(8);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;
                    cell = row.CreateCell(9);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;


                    cell = row.CreateCell(10);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;


                    cell = row.CreateCell(11);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;
                }
                index = index + 300;
                row = sheet.CreateRow(index);

                cell = row.CreateCell(0);
                Excel.SetCellRangeAddress(sheet, index, index + 1, 0, 2);
                cell.SetCellValue("合计(体积m3，活度Bq)");
                cell.CellStyle = cellStyle;



                //row = sheet.GetRow(index);
                //string strEnd = row.GetCell(3).StringCellValue;
                //while (!strEnd.Contains("合计"))
                //{
                //    sheet.RemoveRow(row);
                //    index++;
                //    row = sheet.GetRow(index);
                //    strEnd = row.GetCell(3).StringCellValue;
                //}
                //if (strEnd.Contains("合计"))
                //{
                //    row.GetCell(7).SetCellValue(100000);
                //    row.GetCell(8).SetCellValue(100000);
                //    row.GetCell(9).SetCellValue(100000);
                //    row.GetCell(10).SetCellValue(100000);
                //    row.GetCell(11).SetCellValue(100000);
                //    row.GetCell(12).SetCellValue(100000);
                //    row.GetCell(13).SetCellValue(100000);
                //    row.GetCell(14).SetCellValue(100000);
                //    index++;
                //    row = sheet.GetRow(index);
                //    row.GetCell(8).SetCellValue(700000);
                //}
            }

        }

        private void ReadSheet(ISheet sheet, HSSFWorkbook book, bool tag)
        {
            IRow row;
            int index = 0;
            ICell cell;
            if (true)
            {
                ICellStyle cellStyle = Excel.GetICellStyle(book);

                ICellStyle cellStyleTime = Excel.GetICellStyle(book);
                IDataFormat formatTime = book.CreateDataFormat();
                cellStyleTime.DataFormat = formatTime.GetFormat("m.d");

                ICellStyle cellStyleNum = Excel.GetICellStyle(book);
                IDataFormat formatNum = book.CreateDataFormat();
                cellStyleNum.DataFormat = formatNum.GetFormat("0.00E+00");
                //index = 4;                

                index++;
                for (int i = 0; i < 300; i++)
                {
                    row = sheet.CreateRow(index + i);
                    row.HeightInPoints = 22;
                    cell = row.CreateCell(0);
                    cell.SetCellValue(i + 1);
                    cell.CellStyle = cellStyle;

                    cell = row.CreateCell(1);
                    cell.SetCellValue(1345);
                    cell.CellStyle = cellStyleTime;

                    cell = row.CreateCell(2);
                    cell.SetCellValue("测试");
                    Excel.SetCellRangeAddress(sheet, index + i, index + i, 2, 3);
                    cell.CellStyle = cellStyle;
                    cell = row.CreateCell(3);
                    cell.CellStyle = cellStyle;

                    cell = row.CreateCell(4);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(5);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(6);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(7);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(8);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;
                    cell = row.CreateCell(9);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;


                    cell = row.CreateCell(10);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;


                    cell = row.CreateCell(11);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(12);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(13);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(14);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(15);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(16);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(17);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(18);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(19);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(20);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(21);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(22);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(23);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(24);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;

                    cell = row.CreateCell(25);
                    cell.SetCellValue(123456);
                    cell.CellStyle = cellStyleNum;



                }
            }

        }
    }

 

    public class AllCarAndPingmingAndType {

        public string name { get; set; }
        public string GudaoName { get; set; }

        public List<CarStateus> carStateus { get; set; }
        public int CarStatesCount { get; set; }
        public int ChildrenAllCount { get; set; }
    }
    

    public class CarStateus
    {
        public string StatusName { get; set; }
        public List<CarType> carTypes { get; set; }
        public int carTypesCount { get; set; }

        public int ChildrenAllCount { get; set; }

    }
    public class CarType { 
        public string CarTypeNmae { get; set; }
        public int Count { get; set; }
    }

    public class GudaoCarTypeAndStatusAndCus { 
        public string CarType { get; set; }
        public string Status { get; set; }
        public string Customer { get; set; }
    }

    public class CarBoxTemp
    {
        public string carBoxType { get; set; }
        public string BoxStatus { get; set; }
    }
}
